package T03_PreparedStatementUse.practice;

import T03_PreparedStatementUse.pojo.Customers;
import T05_JDBCUtilPackage.JDBCUtils;
import org.junit.jupiter.api.Test;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Properties;

/**
 * 使用PreparedStatement来替换Statement,实现对数据表的增删改操作
 * 增删改: 查
 */

public class H01_PreparedStatementUpdateTest {

    /**
     *增加
     */
    @Test
    public  void  test01() {
        Connection conn=null;
        PreparedStatement preparedStatement=null;
        try {
            //1.加载配置文件
            InputStream is = H01_PreparedStatementUpdateTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
            Properties pros = new Properties();
            pros.load(is);

            //2.读取配置信息
            String user = pros.getProperty("user");
            String password = pros.getProperty("password");
            String url = pros.getProperty("url");
            String driverClass = pros.getProperty("driver");

            //3.加载驱动
            Class.forName(driverClass);

            //4.获取连接
             conn = DriverManager.getConnection(url,user,password);
            System.out.println(conn);

            //5.预编译sql语句,返回PreparedStatement的实例
            String  sql="insert into customers(name,email,birth) values(?,?,?)";//?:占位符
            preparedStatement = conn.prepareStatement(sql);
            //6.填充占位符
            preparedStatement.setString(1,"迪迦");
            preparedStatement.setString(2,"diJia@qq.com");
            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
            Date parse = format.parse("1000-01-01");
            preparedStatement.setDate(3,new java.sql.Date(parse.getTime()));
            //7.执行操作
            preparedStatement.execute();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ParseException e) {
            e.printStackTrace();
        }finally {
            if(preparedStatement!=null){
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 修改customers表的一条记录
     */
    @Test
    public  void  test02()  {
        Connection connection=null;
        PreparedStatement ps=null;
        try {
            //1.获取数据库的连接
             connection=JDBCUtils.getConnection();
            //2.预编译sql语句,返回PreparedStatement实例
            //定义SQL语句
            String sql="update customers set name=? where id=?";
            ps = connection.prepareStatement(sql);
            //3.填充占位符
            ps.setString(1,"莫扎特");
            ps.setInt(2,18);
            //4.执行
            boolean execute = ps.execute();
            if(execute){
                System.out.println("更新成功");
            }else {
                System.out.println("更新失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection,ps);
        }
    }


    /**
     * 更新一条数据使用 封装工具类
     * @throws Exception
     */
    @Test
    public  void  test03() throws Exception {
            String sql="update customers set name=? where id=?";
            boolean execute = JDBCUtils.update(sql,"莫扎特",1);
            if(execute){
                System.out.println("更新成功");
            }else {
                System.out.println("更新失败");
            }
    }
    /**
     * 删除一条记录
     * @throws Exception
     */
    @Test
    public  void  test04() throws Exception {
        String sql="delete from customers where id=?";
        boolean execute = JDBCUtils.update(sql,1);
        if(execute){
            System.out.println("删除成功");
        }else {
            System.out.println("删除失败");
        }
    }

    /**
     * 查询记录  通用查询操作
     * @throws Exception
     */
    @Test
    public  void  test05() throws Exception {
        ArrayList<Customers> customersArrayList = new ArrayList<>();
        String sql="select * from customers";
        ResultSet rs = JDBCUtils.query(sql,null);
        while (rs.next()){//next() 判断结果集的下一行是否有数据,如果有数据则返回true,并指针下移动 如果返回false 指针不下移动
            //获取当前数据的各个字段的值
            //DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd");
            //LocalDate date = LocalDate.parse(rs.getDate("birth").toString(), df);
            Customers customers=new Customers(rs.getInt("id"),
                    rs.getString("name"),rs.getString("email"),rs.getDate("birth"),rs.getString("photo")
            );
            customersArrayList.add(customers);
        }
        customersArrayList.forEach(v->{
            System.out.println(v);
        });
        //JDBCUtils.closeResource();
    }
}
